﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;

using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace JA.Negocios
{
    public static partial class ModelBO
    {

        public static LinkVO getLink(int idLink, string nome)
        {
            LinkVO linkVO = null;
            List<LinkVO> listLinkVOs = getLinks(idLink, nome);
            if (listLinkVOs != null && listLinkVOs.Count > 0)
            {
                linkVO = listLinkVOs[0];
            }
            return linkVO;
        }

        public static List<LinkVO> getLinks()
        {
            return getLinks(null,null);
        }

        public static List<LinkVO> getLinks(int? idLink, string nome)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT IdLink, Nome, Url, Cadastro, CadastradoPor, Edicao, EditadoPor ");
            query.AppendLine("FROM link ");

            if (idLink != null)
            {
                query.AppendLine(where + "IdLink=?IdLink ");
                where = "AND ";
            }
            if (nome != null)
            {
                query.AppendLine(where + "Nome LIKE ?Nome ");
                where = "AND ";
            }

            query.AppendLine("ORDER BY Nome ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (idLink != null) cmd.Parameters.AddWithValue("?IdLink", idLink);
            if (nome != null) cmd.Parameters.AddWithValue("?Nome", "%" + nome + "%");

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                List<LinkVO> listLink = new List<LinkVO>();

                while (reader.Read())
                {
                    LinkVO linkVO = LinkVO.CreateLinkVO();
                    linkVO.idLink = reader.GetInt32(reader.GetOrdinal("IdLink"));
                    linkVO.nome = reader.GetString(reader.GetOrdinal("Nome"));
                    linkVO.url = reader.GetString(reader.GetOrdinal("Url"));
                    linkVO.cadastro = reader.GetDateTime(reader.GetOrdinal("Cadastro"));
                    linkVO.cadastradoPor = reader.GetString(reader.GetOrdinal("CadastradoPor"));
                    linkVO.edicao = reader.GetDateTime(reader.GetOrdinal("Edicao"));
                    linkVO.editadoPor = reader.GetString(reader.GetOrdinal("EditadoPor"));
                    listLink.Add(linkVO);
                }
                reader.Close();
                return listLink;
            }
            catch (Exception ex){ throw ex; }
            finally { cmd.Connection.Close(); }
        }

        public static void setLink(LinkVO linkVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("UPDATE link ");
            query.AppendLine("SET Nome=?Nome, Url=?Url, Cadastro=?Cadastro, CadastradoPor=?CadastradoPor, Edicao=?Edicao, EditadoPor=?EditadoPor ");
            query.AppendLine("WHERE IdLink=?IdLink ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdLink", linkVO.idLink);
            cmd.Parameters.AddWithValue("?Nome", linkVO.nome);
            cmd.Parameters.AddWithValue("?Url", linkVO.url);
            cmd.Parameters.AddWithValue("?Cadastro", linkVO.cadastro);
            cmd.Parameters.AddWithValue("?CadastradoPor", linkVO.cadastradoPor);
            cmd.Parameters.AddWithValue("?Edicao", linkVO.edicao);
            cmd.Parameters.AddWithValue("?EditadoPor", linkVO.editadoPor);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static long newLink(LinkVO linkVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO link ");
            query.AppendLine("  ( Nome, Url, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
            query.AppendLine("VALUES (?Nome, ?Url, ?Cadastro, ?CadastradoPor, ?Edicao, ?EditadoPor ) ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?Nome", linkVO.nome);
            cmd.Parameters.AddWithValue("?Url", linkVO.url);
            cmd.Parameters.AddWithValue("?Cadastro", linkVO.cadastro);
            cmd.Parameters.AddWithValue("?CadastradoPor", linkVO.cadastradoPor);
            cmd.Parameters.AddWithValue("?Edicao", linkVO.edicao);
            cmd.Parameters.AddWithValue("?EditadoPor", linkVO.editadoPor);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                return cmd.LastInsertedId;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static void delLink(LinkVO linkVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM link ");
            query.AppendLine("WHERE IdLink=?IdLink ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdLink", linkVO.idLink);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }
    }
}
